package com.hotel.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hotel.entity.PointsRecord;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

/**
 * 积分记录Mapper接口
 */
@Mapper
public interface PointsRecordMapper extends BaseMapper<PointsRecord> {
    
    /**
     * 获取会员积分记录
     */
    @Select("SELECT pr.*, m.name as member_name, m.phone as member_phone " +
            "FROM points_record pr " +
            "LEFT JOIN member m ON pr.member_id = m.id " +
            "WHERE pr.member_id = #{memberId} " +
            "ORDER BY pr.create_time DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getMemberPointsRecords(@Param("memberId") Long memberId, @Param("limit") int limit);
    
    /**
     * 获取积分统计
     */
    @Select("SELECT " +
            "COUNT(*) as total_records, " +
            "SUM(CASE WHEN points > 0 THEN points ELSE 0 END) as total_earned, " +
            "SUM(CASE WHEN points < 0 THEN ABS(points) ELSE 0 END) as total_spent, " +
            "SUM(points) as net_points, " +
            "COUNT(DISTINCT member_id) as active_members " +
            "FROM points_record " +
            "WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)")
    Map<String, Object> getPointsStats();
    
    /**
     * 获取积分趋势
     */
    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date, " +
            "SUM(CASE WHEN points > 0 THEN points ELSE 0 END) as earned_points, " +
            "SUM(CASE WHEN points < 0 THEN ABS(points) ELSE 0 END) as spent_points, " +
            "COUNT(*) as record_count " +
            "FROM points_record " +
            "WHERE DATE(create_time) BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') " +
            "ORDER BY date ASC")
    List<Map<String, Object>> getPointsTrend(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    /**
     * 获取指定日期范围内的积分趋势 (与getPointsTrend逻辑可能重复，保留一个或根据具体需求调整)
     */
    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date, " +
            "SUM(CASE WHEN points > 0 THEN points ELSE 0 END) as earned_points, " +
            "SUM(CASE WHEN points < 0 THEN ABS(points) ELSE 0 END) as spent_points, " +
            "COUNT(*) as record_count " +
            "FROM points_record " +
            "WHERE DATE(create_time) BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') " +
            "ORDER BY date ASC")
    List<Map<String, Object>> selectPointsTrendByDateRange(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    /**
     * 获取月度积分统计
     */
    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m') as month, " +
            "SUM(CASE WHEN points > 0 THEN points ELSE 0 END) as total_earned, " +
            "SUM(CASE WHEN points < 0 THEN ABS(points) ELSE 0 END) as total_spent, " +
            "SUM(points) as net_points, " +
            "COUNT(*) as record_count " +
            "FROM points_record " +
            "WHERE DATE(create_time) BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m') " +
            "ORDER BY month ASC")
    List<Map<String, Object>> selectMonthlyPointsStats(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    /**
     * 获取指定会员在指定日期范围内的积分趋势
     */
    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date, " +
            "SUM(points) as daily_points_change, " +
            "(SELECT SUM(pr2.points) FROM points_record pr2 WHERE pr2.member_id = #{memberId} AND DATE(pr2.create_time) <= DATE(pr.create_time)) as running_total_points " +
            "FROM points_record pr " +
            "WHERE pr.member_id = #{memberId} AND DATE(pr.create_time) BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY DATE_FORMAT(pr.create_time, '%Y-%m-%d') " +
            "ORDER BY date ASC")
    List<Map<String, Object>> selectMemberPointsTrendByDateRange(@Param("memberId") Long memberId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

}